package cn.uniquetech.esf;

import cn.uniquetech.util.FileUtil;

import java.util.List;

public class GenSqlUtil {
	private static String headerStr = "\t";
	private static String tailStr = "\r\n";
	private static String key = "iid";

	public static String paraseSql(String tableName, String voName, String tableRemarks, List<ColumnVO> columnList) {

		String packageName = GenerateCodeSqlServer.PACKAGENAME;
		String servicePackage = packageName + ".sqlmap";
		String entityName = packageName + ".vo." + voName + "VO";// +
		String xmlStr = ""; // ""VO"";
		xmlStr = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + tailStr;
		xmlStr += "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">" + tailStr;
		xmlStr += "<mapper namespace=\"" + voName + "Mapper\">" + tailStr;

		//
		xmlStr += headerStr + "<!-- " + tableRemarks + " -->" + tailStr;
		// addEntity
		xmlStr += headerStr + "<!-- 插入实体 -->" + tailStr;
		xmlStr += headerStr + "<insert id=\"save\" parameterType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + " " + genInsertSqlStr(tableName, columnList) + tailStr;
		xmlStr += headerStr + "</insert>" + tailStr;

		// updateEntity
		xmlStr += headerStr + "<!-- 更新实体 -->" + tailStr;
		xmlStr += headerStr + "<update id=\"edit\" parameterType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + " " + genUpdateSqlStr(tableName, columnList) + tailStr;
		xmlStr += headerStr + "</update>" + tailStr;

		// deleteEntity
		xmlStr += headerStr + "<!-- 删除实体-->" + tailStr;
		xmlStr += headerStr + "<delete id=\"removeOne\" parameterType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + " delete from " + tableName + " where " + key + " = #{" + key + "}" + tailStr;
		xmlStr += headerStr + "</delete>" + tailStr;
		// deleteEntity
		xmlStr += headerStr + "<!-- 删除多个实体-->" + tailStr;
		xmlStr += headerStr + "<delete id=\"removeItems\" parameterType=\"string\">" + tailStr;
		xmlStr += headerStr + " delete from " + tableName + " where " + key + " in (${_parameter})" + tailStr;
		xmlStr += headerStr + "</delete>" + tailStr;

		// fecthOneEntity
		xmlStr += headerStr + "<!-- 获取单个实体 -->" + tailStr;
		xmlStr += headerStr + "<select id=\"fecthOne\" parameterType=\"" + entityName + "\" resultType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + " select * from " + tableName + " where " + key + " = #{" + key + "}" + tailStr;
		xmlStr += headerStr + "</select>" + tailStr;

		// queryEntities
		xmlStr += headerStr + "<!-- 查询实体集合 -->" + tailStr;
		xmlStr += headerStr + "<select id=\"getItems\" parameterType=\"" + entityName + "\" resultType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + " select * from " + tableName + " order by " + key + " desc" + tailStr;
		xmlStr += headerStr + "</select>" + tailStr;

		// 分页查询
		xmlStr += headerStr + "<!-- 分页查询 -->" + tailStr;
		xmlStr += headerStr + "<select id=\"pageQuery\" parameterType=\"" + entityName + "\" resultType=\"" + entityName + "\">" + tailStr;
		xmlStr += headerStr + "select A.* from (select row_number() over(order by " + key + " desc) as rownum," + tableName + ".* from " + tableName + " where 1=1 <if test=\"startdate !=null and startdate !='' \"> and adddate &gt;= #{startdate}</if><if test=\"enddate !=null and enddate !='' \"> and adddate &lt;= #{enddate}</if> ) A where A.rownum &gt;#{rowStartNo} and A.rownum &lt;= #{rowEndNo} "
				+ tailStr;
		xmlStr += headerStr + "</select>" + tailStr;

		xmlStr += headerStr + "<select id=\"pageQuery_count\" parameterType=\"" + entityName + "\" resultType=\"int\">" + tailStr;
		xmlStr += headerStr + " select count(*) from " + tableName + " where 1=1 <if test=\"startdate !=null and startdate !='' \"> and adddate &gt;= #{startdate}</if><if test=\"enddate !=null and enddate !='' \"> and adddate &lt;= #{enddate}</if> " + tailStr;
		xmlStr += headerStr + "</select>" + tailStr;

		xmlStr += "</mapper>";
		FileUtil.writeFile(servicePackage, voName + ".xml", xmlStr);
		return xmlStr;
	}

	private static String genInsertSqlStr(String tableName, List<ColumnVO> columnList) {
		String sqlStr = "insert into " + tableName + " (";
		String fields = "";
		String values = "";

		for (ColumnVO item : columnList) {
			String colName = item.getColName();
			if (key.equals(colName)) {
				continue;
			}
			fields += colName + ",";
			values += "#{" + colName + "},";
		}

		fields = fields.substring(0, fields.length() - 1);
		values = values.substring(0, values.length() - 1);
		sqlStr += fields + ") " + tailStr;
		sqlStr += headerStr + " values " + tailStr;
		sqlStr += headerStr + "(" + values + ")";
		return sqlStr;
	}

	private static String genUpdateSqlStr(String tableName, List<ColumnVO> columnList) {
		String sqlStr = "update " + tableName + " set ";

		String fields = "";

		for (ColumnVO item : columnList) {
			String colName = item.getColName();
			if (key.equals(colName) || "icreator".equals(colName) || "ccreator".equals(colName) || "dcreator".equals(colName)) {
				continue;
			}
			fields += colName + "=#{" + colName + "},";
		}

		fields = fields.substring(0, fields.length() - 1);
		sqlStr += fields + "\r\n";
		sqlStr += headerStr + " where " + key + " =#{" + key + "}";
		return sqlStr;
	}

}
